const { pool } = require('../config/db');

// 创建订单表
exports.createOrderTable = async () => {
  const query = `
    CREATE TABLE IF NOT EXISTS orders (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      goods_id INT NOT NULL,
      goods_info JSON NOT NULL,
      total_price DECIMAL(10, 2) NOT NULL,
      status VARCHAR(20) DEFAULT '待支付',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id),
      FOREIGN KEY (goods_id) REFERENCES goods(id)
    )
  `;
  
  try {
    await pool.execute(query);
    console.log('Order table created successfully!');
  } catch (error) {
    console.error('Error creating order table:', error);
  }
};

// 创建订单
exports.createOrder = async (orderData) => {
  // 防御性编程，确保所有参数都有值
  const safeOrderData = {
    user_id: orderData.user_id || 0,
    goods_id: orderData.goods_id || 0,
    goods_info: orderData.goods_info || {},
    total_price: orderData.total_price || 0.00
  };
  
  const { user_id, goods_id, goods_info, total_price } = safeOrderData;
  const query = `INSERT INTO orders (user_id, goods_id, goods_info, total_price) VALUES (${user_id}, ${goods_id}, '${JSON.stringify(goods_info).replace(/'/g, "''")}', ${total_price})`;
  
  try {
    // 确保没有undefined值传递给SQL
    const [result] = await pool.execute(query);
    
    return this.getOrderById(result.insertId);
  } catch (error) {
    console.error('创建订单时发生SQL错误:', error);
    throw error;
  }
};

// 根据ID获取订单详情
exports.getOrderById = async (id) => {
  const query = `SELECT * FROM orders WHERE id = ${id}`;
  const [rows] = await pool.execute(query);
  return rows[0] || null;
};

// 根据订单编号获取订单详情
exports.getOrderByOrderNo = async (orderNo) => {
  // 从订单编号中提取ID部分
  // 订单编号格式为: ORD00000001
  const idMatch = orderNo.match(/ORD(\d+)/i);
  if (!idMatch || !idMatch[1]) {
    throw new Error('无效的订单编号格式');
  }
  
  const orderId = parseInt(idMatch[1]);
  if (isNaN(orderId)) {
    throw new Error('订单编号中的ID不是有效的数字');
  }
  
  return this.getOrderById(orderId);
};

// 获取用户的订单列表
exports.getUserOrders = async (userId, status = null) => {
  let query = `SELECT * FROM orders WHERE user_id = ${userId}`;
  
  if (status) {
    query += ` AND status = '${status}'`;
  }
  
  query += ' ORDER BY created_at DESC';
  const [rows] = await pool.execute(query);
  return rows;
};

// 更新订单状态
exports.updateOrderStatus = async (orderId, status) => {
  const query = `UPDATE orders SET status = '${status}' WHERE id = ${orderId}`;
  await pool.execute(query);
  
  return this.getOrderById(orderId);
};

// 删除订单
exports.deleteOrder = async (orderId, userId) => {
  // 先检查订单是否存在且属于当前用户
  const order = await this.getOrderById(orderId);
  if (!order) {
    throw new Error('订单不存在');
  }
  
  if (order.user_id !== userId) {
    throw new Error('无权删除该订单');
  }
  
  // 执行删除操作
  const query = `DELETE FROM orders WHERE id = ${orderId}`;
  await pool.execute(query);
  
  return { success: true, message: '订单已删除' };
};
